VBA automation code

VBA code is used to create and fill documents with data from the Access database using VBA automation. This code is generated by the document creation process wizard. This page explains code blocks commonly appearing in the generated code.

Create a document instance from the template


Dim appWord As Word.Application: Set appWord = New Word.Application
Dim doc As Word.Document
Set doc = appWord.Documents.Add(Template:=strTemplateFile, Visible:=True)

Transfer data from the source (form) into the document


Const cstrObjectName As String = "Invoice"
Dim frm As Form: Set frm = Forms(cstrObjectName)
With frm
    Dim wfr As New WordFiller: Set wfr.Document = doc
    wfr.FillElement Bookmark:="Address", value:=ControlValue(Control:=![Address])
    wfr.FillElement Bookmark:="AmountDue", value:=ControlValue(Control:=![Amount Due])
    wfr.FillElement Bookmark:="AmountMaterials", value:=ControlValue(Control:=![Amount Materials])
    wfr.FillElement Bookmark:="DueDate", value:=ControlValue(Control:=![Due Date], Format:="dd mmm yy")
    wfr.SetCheckbox Bookmark:="Extendedservice", value:=ControlValue(Control:=![Extended service])
'...
    wfr.FillTableFromArray Bookmark:="LaborLinesubform", var2D:=arr2D

The WordFiller class handles the complexity of moving the data from the form into the marked positions in the document:

FillElement for simple values
FillTableFromArray inserts rows with data into the table
SetCheckbox handles checkboxes in case of Yes/No fields.

The ControlValue function


Public Function ControlValue(Control As Object, Optional UseDisplayValue As Boolean = True, 
                            Optional Format As String = "") As Variant

hides complexities in getting values from different types of controls. As an example if a group box contains several option buttons it assigns the selected option as the value of the group box.
Allows you to use the Display value (default) or Bound value (UseDisplayValue = False). The latter may be useful if
- you want the unformatted value,
- the control has a Lookup property specified, but you want to have the original value, e.g. Company ID instead of Company name.
supports formatting of values, notably important in case of dates. The formatting expression applied is set under preferences.

Get data from subforms to fill tables


Dim rs As Recordset
Dim intRow As Integer
Set rs = ![LaborLine subform].Form.RecordsetClone
rs.MoveLast
rs.MoveFirst
ReDim arr2D(0 To rs.RecordCount - 1, 0 To 3)
intRow = 0
Do Until rs.EOF
    ![LaborLine subform].Form.Bookmark = rs.Bookmark
    arr2D(intRow, 0) = ControlValue(Control:=![LaborLine subform].Form![Labor])
    arr2D(intRow, 1) = ControlValue(Control:=![LaborLine subform].Form![Hours])
    arr2D(intRow, 2) = ControlValue(Control:=![LaborLine subform].Form![Rate Per Hour])
    arr2D(intRow, 3) = ControlValue(Control:=![LaborLine subform].Form![TotalLaborCosts])
    intRow = intRow + 1
    rs.MoveNext
Loop

Data to be transferred from a subform control source to a table in the document is first collected into an array. In the above case the 2nd dimension of the array is 0 to 3 because the wizard had been informed by the Template Designer that the number of columns to be filled is 4.

Save the new document


Dim strFullFileName As String
Const cstrExtension As String = ".docx"
Dim strFolder As String
Dim strFileName As String
strFileName = ReplaceIllegalCharacters(ControlValue(![CustomerID]), "_") & _
ReplaceIllegalCharacters(ControlValue(![Invoice Date], Format:="yymmdd"), "_")
strFolder = CodeProject.Path & "\Documents"
strFullFileName = strFolder & "\" & strFileName & cstrExtension
'...
doc.SaveAs FileName:=strFullFileName, FileFormat:=wdFormatXMLDocument
Invoice_time_and_materials_Current = strFullFileName
RecordDocument strFullFileName, strTemplateFile, "Invoice_time_and_materials_Current"
doc.Saved = True

Code above shows the code produced based on choices made in the wizard in the demo concerning the folder and filename.

Save document as Pdf

In the wizard you can specify you want the document saved as type Pdf. This requires a different code for the saving process, the ExportAsFixedFormat method:


doc.ExportAsFixedFormat OutputFileName:=strFullFileName, & _ 
        ExportFormat:=wdExportFormatPDF, OpenAfterExport:=True

This involves an intermediate document in .docx format, which in most cases you will want to close and delete after the Pdf was created:


doc.Close False

Returns the full path of the saved document

Having the file full name returned is a key to integrating the document creation process into a larger process, for example having the document as an attachment for an email.


Invoice_time_and_materials_PDF_Current = strFullFileName

Recording the created document in an Access table

To have the document easily available in the Open Documents

Error handling


HandleError:
    Select Case Err.Number
    Case 5356 'Word cannot save this file because it is already open elsewhere
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
        Resume Next
    Case 3021 'No current record
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
        Resume HandleExit
    End Select
    ErrorHandle Err, "Invoice_time_and_materials_PDF_Current"
    Resume HandleExit
End Function

At the end of the document procedure error handling is inserted depending on the current applicable scenario as defined in the wizard.